package com.uu.safeguard.utils;


import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import java.sql.*;
import java.util.*;


@Component
public class DbUtil
{
    private Logger logger = LoggerFactory.getLogger(DbUtil.class);

    //数据库驱动类
    private static String Driver;

    // 数据库连接地址
    private static String URL;

    // 数据库的用户名
    private static String UserName;

    // 数据库的密码
    private static String Password;

    //表名称
    private String Table;

    //sql条件
    private List<List<Object>> Where = new ArrayList<List<Object>>();

    //sql排序
    private String Order = " create_time desc ";

    //sql页大小
    private int Limit = 100;

    //sql页码
    private int Page = 1;

    //sql语句
    private String Sql = "";

    //
    private List<Object> Parameter = new ArrayList<Object>();;

    @Autowired
    private Environment env;

    @PostConstruct
    public void config() {
        Driver = env.getProperty("spring.datasource.driver-class-name");
        URL = env.getProperty("spring.datasource.url");
        UserName = env.getProperty("spring.datasource.username");
        Password = env.getProperty("spring.datasource.password");
    }




    public Connection getConnection() {
        try {
            Class.forName(Driver); // 加载驱动
        } catch (ClassNotFoundException e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        try {
            //通过DriverManager类的getConenction方法指定三个参数,连接数据库
            Connection connection = DriverManager.getConnection(URL, UserName, Password);
            return connection;
        } catch (SQLException e) {
            // TODO: handle exception
            e.printStackTrace();
            return null;
        }
    }

    public DbUtil table(String table) {
        Table = " " + table + " ";
        return this;
    }

    public void setParams(Object x){
        Parameter.add(x);
    }

    public DbUtil where(List list){
        if(!list.isEmpty()){
            Where.addAll(list);
        }
        return this;
    }

    public DbUtil order(String order){
        if(!order.isEmpty()){
            Order =  order;
        }
        return this;
    }

    public DbUtil limit(int limit){
        if(limit > 0){
            Limit = limit;
        }
        return this;
    }

    public DbUtil page(int page){
        if(page > 0){
            Page = page;
        }
        return this;
    }

    ////清空查询条件、排序、分页、sql绑定参数
    private void clearWhereAndOrderAndPageAndParameter(){
        Where = new ArrayList<>();
        Order = " create_time desc ";
        Limit = 100;
        Page = 1;
        Parameter = new ArrayList<Object>();
    }

    public String getLastSql(){
        return Sql;
    }



    //更新
    public int update(Map<Object, Object> setMap){

        //组装sql语句
        String sql = "UPDATE " + Table + " SET ";

        //set
        if(setMap.isEmpty()){
           return 0;
        }
        for(Map.Entry<Object, Object> entry : setMap.entrySet()){
            Object mapKey = entry.getKey();
            Object mapValue = entry.getValue();
            sql += " " + mapKey + " = ? , ";
            setParams(mapValue);
        }

        sql = sql.substring(0, sql.length() - 2) + " ";

        sql += buildWhere(); //组装where 语句

        return executeUpdate(sql, Parameter);//DML操作
    }


    //插入
    public int insert (Map<Object, Object> setMap){

        //set
        if(setMap.isEmpty()){
            return 0;
        }
        String sql_fields = " ( ";
        String sql_values = " ( ";
        for(Map.Entry<Object, Object> entry : setMap.entrySet()){
            Object mapKey = entry.getKey();
            Object mapValue = entry.getValue();
            sql_fields += " " + mapKey + " , ";
            sql_values += " ? , ";
            setParams(mapValue);
        }

        sql_fields = sql_fields.substring(0, sql_fields.length() - 2) + " ";
        sql_values = sql_values.substring(0, sql_values.length() - 2) + " ";

        sql_fields += " ) ";
        sql_values += " ) ";

        //组装sql语句
        String sql = "INSERT INTO " + Table + " " + sql_fields + " VALUES " + sql_values;

        return executeUpdate(sql, Parameter);//DML操作
    }


    //插入多条
    public int insertAll(List<Map> insertList){

        //set
        if(insertList.isEmpty()){
            return 0;
        }

        String sql_fields = " ( ";
        String sql_values = "";
        for (int i = 0; i < insertList.size(); i ++){
            Map<Object, Object> setMap = insertList.get(i);

            sql_values += " ( ";
            for(Map.Entry<Object, Object> entry : setMap.entrySet()){
                Object mapKey = entry.getKey();
                Object mapValue = entry.getValue();

                if(i == 0){
                    sql_fields += " " + mapKey + " , ";
                }

                sql_values += " ? , ";
                setParams(mapValue);
            }

            sql_values = sql_values.substring(0, sql_values.length() - 2) + " ";
            sql_values += " ) , ";
        }

        sql_fields = sql_fields.substring(0, sql_fields.length() - 2) + " ";
        sql_values = sql_values.substring(0, sql_values.length() - 2) + " ";

        sql_fields += " ) ";

        //组装sql语句
        String sql = "INSERT INTO " + Table + " " + sql_fields + " VALUES " + sql_values;

        return executeUpdate(sql, Parameter);//DML操作
    }


    //删除数据
    public int delete(){

        //组装sql语句
        String sql = "DELETE FROM " + Table + " ";

        sql += buildWhere(); //组装where 语句

        return executeUpdate(sql, Parameter);//DML操作

    }

    //组装where条件语句
    private String buildWhere(){
        String whereSql = "";
        if(!Where.isEmpty()){
            whereSql = " WHERE 1 = 1 ";
            for(List x : Where) {
                String param0 = x.get(0).toString(); //第一个参数：字段名
                String param1 = x.get(1).toString(); //第二个参数：运算符
                String param2 = x.get(2).toString(); //第三个参数：字段值
                String logical = "AND"; //默认AND条件

                //如果第三个个参数包含 |
                List param2List = new ArrayList();
                if(param2.contains("|")){
                    String[] param2Arr = param2.split("\\|");
                    param2List = Arrays.asList(param2Arr.clone());
                }else{
                    param2List.add(param2);
                }

                if(x.size() >= 4){ //如果有第四个参数：逻辑运算符，且为OR 则为OR条件
                    String param3 = x.get(3).toString();
                    if(param3.toUpperCase().equals("OR")){
                        logical = "OR";
                    }
                }

                whereSql += " " + logical + " ( ";

                for (int i = 0; i < param2List.size(); i ++){
                    if(i > 0){
                        whereSql += " OR ";
                    }
                    if(param1.toUpperCase().equals("BETWEEN")) { //如果是 between查询
                        String[] betweenArr = param2List.get(i).toString().split(",");
                        whereSql += param0 + " " + param1 + " ? AND ? ";
                        setParams(betweenArr[0]);
                        setParams(betweenArr[1]);
                    }else if(param1.toUpperCase().equals("IN") || param1.toUpperCase().equals("NOT IN")){
                        whereSql += param0 + " " + param1 + " (  ";
                        String[] inArr = param2List.get(i).toString().split(",");
                        for(int j = 0; j < inArr.length; j ++ ){
                            if(j > 0){
                                whereSql += " , ";
                            }
                            whereSql += " ? ";
                            setParams(inArr[j]);
                        }
                        whereSql += " ) ";

                    }else if(param1.toUpperCase().equals("IS") ){
                        whereSql += param0 + " " + param1 + " " + param2 + " ";
                    }
                    else{
                        whereSql += param0 + " " + param1 + " ? ";
                        setParams(param2List.get(i).toString());
                    }
                }

                whereSql += " ) ";

            }
        }

        return whereSql;
    }

    //设置stmp参数
    private void setStmpParams(PreparedStatement pstm, List parameter) throws SQLException {
        for (int i = 0;i < parameter.size();i ++){
            pstm.setObject((i + 1), parameter.get(i));
        }
    }

    //组装order语句
    private String buildOrder(){
        String orderSql = "";
        if(!Order.isEmpty()){
            //排序
            orderSql += " ORDER BY " + Order + " ";
        }
        return orderSql;
    }

    //组装分页语句
    private String buildPage(){
        String pageSql = "";
        int offset = (Page - 1) * Limit;
        pageSql += " OFFSET ? ROWS FETCH NEXT ?  ROWS ONLY ";
        setParams(offset);
        setParams(Limit);
        return pageSql;
    }

    //生成真实sql
    private String buildSql(String sql, List parameter){
        String showSql = "";

        for (int i = 0; i < parameter.size(); i ++){
            String newStr = parameter.get(i).toString();
            sql = sql.replaceFirst("\\?",newStr);
        }

        return sql;
    }



    //查询列表
    public List select(String fields) {
        String fields_sql = fields.isEmpty() ? "*" : "" + fields + "";

        //组装sql语句
        String sql = "SELECT " + fields_sql + " FROM ";

        //表名称
        sql += " " + Table + " ";

        sql += buildWhere(); //组装where 语句
        sql += buildOrder(); //组装order 语句

        //分页
        sql += buildPage(); //组装分页 语句

        List reList = executeQuery(sql, Parameter);
        return reList;


    }

    //查询记录条数
    public int count(String fields){
        String fields_sql = fields.isEmpty() ? "COUNT(*)" : "COUNT(" + fields + ") AS count";
        //组装sql语句
        String sql = "SELECT " + fields_sql + " FROM ";
        //表名称
        sql += " " + Table + " ";
        sql += buildWhere(); //组装where 语句
        List reList = executeQuery(sql, Parameter);
        Map reMap = (Map) reList.get(0);
        int count = (int) reMap.get("count");
        return count;
    }

    private List convertList(ResultSet rs) throws SQLException{
        List list = new ArrayList();
        ResultSetMetaData md = rs.getMetaData();//获取键名
        int columnCount = md.getColumnCount();//获取行的数量
        while (rs.next()) {
            Map rowData = new HashMap();//声明Map
            for (int i = 1; i <= columnCount; i++) {
                rowData.put(md.getColumnName(i), rs.getObject(i));//获取键名及值
            }
            list.add(rowData);
        }
        return list;
    }


    public Map find(String fields) {
        List reList = select(fields);
        if(reList.size() == 0){
            return new HashMap();
        }
        Map reMap = (Map) reList.get(0);
        return reMap;

    }

    //DQL操作
    private List executeQuery (String sql, List parameter) {

        Sql = buildSql(sql, parameter);//生成真是sql

        Connection conn = getConnection();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List reList = null;
        try{
            pstm = conn.prepareStatement(sql);
            /*for (int i = 0;i < parameter.size();i ++){
                pstm.setObject((i + 1), parameter.get(i));
            }*/
            setStmpParams(pstm, parameter);
            rs = pstm.executeQuery();
            reList = convertList(rs);
            return reList;
        }catch(Exception e){
            e.printStackTrace();
            return reList;
        } finally {
            clearWhereAndOrderAndPageAndParameter(); //清空查询条件、排序、分页、sql绑定参数
            try {
                if(rs != null){ rs.close(); }
                if(pstm != null){ pstm.close(); }
                if(conn != null){ conn.close(); }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    //DML操作
    private int executeUpdate(String sql, List parameter){

        Sql = buildSql(sql, parameter);//生成真是sql
        Connection conn = getConnection();
        PreparedStatement pstm = null;
        int rs = 0;
        try{
            pstm = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            /*for (int i = 0;i < parameter.size();i ++){
                pstm.setObject((i + 1), parameter.get(i));
            }*/
            setStmpParams(pstm, parameter);
            rs = pstm.executeUpdate();
            ResultSet generatedKeys = pstm.getGeneratedKeys();
            int anInt = 0;
            if(generatedKeys.next()){
                anInt = generatedKeys.getInt(1);
            }
            if(anInt == 0){
                return rs;
            }else{
                return anInt;
            }

        }catch(Exception e){
            e.printStackTrace();
            return rs;
        } finally {
            try {
                clearWhereAndOrderAndPageAndParameter(); //清空查询条件、排序、分页、sql绑定参数
                if(pstm != null){ pstm.close(); }
                if(conn != null){ conn.close(); }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
    }


    //DQL操作
    public List executeQueryBySql (String sql, List parameter) {

        Sql = buildSql(sql, parameter);//生成真是sql

        Connection conn = getConnection();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List reList = null;
        try{
            pstm = conn.prepareStatement(sql);
            setStmpParams(pstm, parameter);

            rs = pstm.executeQuery();
            reList = convertList(rs);
            return reList;
        }catch(Exception e){
            e.printStackTrace();
            return reList;
        } finally {
            clearWhereAndOrderAndPageAndParameter(); //清空查询条件、排序、分页、sql绑定参数
            try {
                if(rs != null){ rs.close(); }
                if(pstm != null){ pstm.close(); }
                if(conn != null){ conn.close(); }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    //DML操作
    public int executeUpdateBySql(String sql, List parameter){

        Sql = buildSql(sql, parameter);//生成真是sql

        Connection conn = getConnection();
        PreparedStatement pstm = null;
        int rs = 0;
        try{
            pstm = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            setStmpParams(pstm, parameter);
            rs = pstm.executeUpdate();
            ResultSet generatedKeys = pstm.getGeneratedKeys();
            int anInt = 0;
            if(generatedKeys.next()){
                anInt = generatedKeys.getInt(1);
            }
            if(anInt == 0){
                return rs;
            }else{
                return anInt;
            }

        }catch(Exception e){
            e.printStackTrace();
            return rs;
        } finally {
            try {
                clearWhereAndOrderAndPageAndParameter(); //清空查询条件、排序、分页、sql绑定参数
                if(pstm != null){ pstm.close(); }
                if(conn != null){ conn.close(); }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
    }

    //调用存储过程
    public Object executeCall(String sql, List parameter, int type){

        Sql = buildSql(sql, parameter);//生成真是sql

        if(type == 1){
            sql = "{ ? = call " + sql + " }";
        }else{
            sql = "{ call " + sql + " }";
        }

        Connection conn = getConnection();
        CallableStatement cstm = null;
        Object rs = null;
        try{
            cstm = conn.prepareCall(sql);
            if(type == 1){
                cstm.registerOutParameter(1, Types.INTEGER);
                for (int i = 0;i < parameter.size();i ++){
                    cstm.setObject((i + 2), parameter.get(i));
                }
            }else{
                setStmpParams(cstm, parameter);
            }
            rs = cstm.execute();
            if(type == 1){
                return cstm.getObject(1);
            }else{
                return rs;
            }
        }catch(Exception e){
            e.printStackTrace();
            return rs;
        } finally {
            try {
                clearWhereAndOrderAndPageAndParameter(); //清空查询条件、排序、分页、sql绑定参数
                if(cstm != null){ cstm.close(); }
                if(conn != null){ conn.close(); }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
    }

}